<?php

/**
 * @author ducnhat
 * @email ddnhat@gmail.com
 * @copyright 2014
 */

class Admin_Model_SaleTotal extends Zend_Db_Table{
    public function saveData($data){
    	$db = Zend_Registry::get('db');
        
        $db->beginTransaction();
        
        try{
            if(isset($data['id']) && $this->checkExists($data['id'])){
                $id = $data['id'];
                $db->update("sale-total", $data, "id = $id");
            }else{
                $db->insert('sale-total', $data);
                $id = $db->lastInsertId("sale-total");
            }
            
            $db->commit();
            
            return $id;
        }catch(Exception $e){
            $db->rollBack();
            
            return false;
        }
    }
    
    public function getDatas(){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $result = array();
        
        $select = $db->select()
                    ->from(array('s' => 'sale-total'), array('id', 'name as name_id', 'date', 'receiver', 'total', 'totalpay'))
                    ->join(array('c' => 'customers'), "s.name = c.id", array('name'))
                    ->where('s.deleted = 0')
                    ->order('s.date DESC')
                    ->order('s.id DESC');
        
        $rs = $db->fetchAll($select);
        $result = array();
        
        foreach($rs as $row){
            $dept = $this->getDept($row['name_id'], $row['date'], $row['id']);
            $remaining = $row['total'] + $dept - $row['totalpay'];
            
            $row['dept'] = $dept;
            $row['remaining'] = $remaining;
            $result[] = $row;
        }
        
        return $result;
    }
    
    public function getData($id){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $result = array();
        
        $select = $db->select()
                    ->from(array('s' => 'sale-total'), array('id', 'name', 'date', 'total', 'totalpay', 'receiver', 'dept', 'remaining'))
                    ->where('s.deleted = 0')
                    ->where('s.id = ?', $id);
        
        $result = $db->fetchRow($select);
        
        $dept = $this->getDept($result['name'], $result['date'], $result['id']);
        $remaining = $result['total'] + $dept - $result['totalpay'];
        
        $result['dept'] = $dept;
        $result['remaining'] = $remaining;
        
        return $result;
    }
    
    public function getDept($customer_id, $date, $id = null){
        $db = Zend_Registry::get('db');
        $db->setFetchMode(Zend_Db::FETCH_ASSOC);
        
        $result = array();
        
        $select = $db->select()
                    ->from(array('s' => 'sale-total'), array('SUM(total) - SUM(totalpay) AS dept')); 
                                       
        $where = "(s.date <= '$date' AND s.name = $customer_id)";
        
        if($id != null){
            $where = "((s.date < '$date' AND s.name = $customer_id AND s.id != $id)";
            $where .= " OR (s.date = '$date' AND s.name = $customer_id AND s.id < $id))";
        }
        
        $where .= " AND (s.deleted = 0)";
        
        $select->where($where);
        
        $rs = $db->fetchRow($select);
        
        if($rs == null){
            $result = 0;
        }else{
            $result = current($rs);
        }
        
        return $result;
    }
    
    public function deleteData($id){
        $db = Zend_Registry::get('db');
        
        $db->beginTransaction();
        
        try{
            $order = $this->getData($id);
            $total = $order['total'];
            $date = $order['date'];
            $name = $order['name'];
            
            $data1 = array('dept' => new Zend_Db_Expr("dept - $total"),
                            'remaining' => new Zend_Db_Expr("remaining - $total")
                            );
                            
            $db->update('sale-total', $data1, "(name = $name AND date > '$date') OR (id > $id AND date = '$date')");
            
            $data2['deleted'] = 1;
            $db->update("sale-total", $data2, "id = $id");
            $db->commit();
            
            return $id;
        }catch(Exception $e){
            $db->rollBack();
            
            return false;
        }
    }
    
    public function checkExists($id){
        $option = array('table' => 'sale-total', 'field' => 'id');
    	$validator = new Zend_Validate_Db_RecordExists($option);
        
        if($validator->isValid($id)){
            return true;
        }
        
        return false;
    }
}